﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Xml;

namespace DataImport
{
    class Program
    {
        static void Main(string[] args)
        {
            //7A7540100991F0B19872DC556024DE8C_S\VISUAL STUDIO 2008\PROJECTS\ANTIOBESITYSERVICES\ANTIOBESITY.SERVICES\APP_DATA\ANTIOBESITY.MDF
            var connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AntiObesity.mdf;User Id=antiob;Password=antiob";
            var xmlPath = @"C:\Dummy\data.xml";
            AppDomain.CurrentDomain.SetData("DataDirectory",@"C:\Documents and Settings\Shayan\My Documents\Visual Studio 2008\Projects\AntiObesityServices\AntiObesity.Services\App_Data");
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                var insertCategory = new SqlCommand("if not exists (select * from Categories where [name] = @CATEGORY_NAME)INSERT INTO Categories ([id],[name]) VALUES(@ID, @CATEGORY_NAME); SELECT [id] FROM Categories WHERE [name]=@CATEGORY_NAME;", conn);
                insertCategory.Parameters.Add("@ID", System.Data.SqlDbType.UniqueIdentifier);
                insertCategory.Parameters.Add("@CATEGORY_NAME", System.Data.SqlDbType.NVarChar);

                var insertFood = new SqlCommand("if not exists (select * from Foods where [name] = @FOOD_NAME)INSERT INTO Foods ([id],[name],[categoryID],[caleries]) VALUES(@ID, @FOOD_NAME, @CATEGORY_ID, @CALORIES)", conn);
                insertFood.Parameters.Add("@ID", System.Data.SqlDbType.UniqueIdentifier);
                insertFood.Parameters.Add("@FOOD_NAME", System.Data.SqlDbType.NVarChar);
                insertFood.Parameters.Add("@CATEGORY_ID", System.Data.SqlDbType.UniqueIdentifier);
                insertFood.Parameters.Add("@CALORIES", System.Data.SqlDbType.Float);


                Console.WriteLine("DB opened");

                string categoryName = null;
                Guid categoryID = Guid.Empty;
                string foodName = null;
                float calories = 0.0f;

                using (var reader = XmlReader.Create(xmlPath))
                {
                    while (reader.Read())
                    {
                        if (reader.NodeType != XmlNodeType.Element)
                            continue;

                        if (reader.Name == "FdGrp_Desc")
                        {
                            categoryName = reader.ReadString();
                            Console.WriteLine("Category:{0}", categoryName);

                            insertCategory.Parameters["@ID"].Value = Guid.NewGuid();
                            insertCategory.Parameters["@CATEGORY_NAME"].Value = categoryName;
                            categoryID = (Guid)insertCategory.ExecuteScalar();
                            continue;
                        }

                        if (reader.Name == "Long_Desc")
                        {
                            foodName = reader.ReadString();
                            Console.Write(" Food: [{0}]", foodName);
                            continue;
                        }

                        if (reader.Name == "Energ_Kcal")
                        {
                            calories = (float)Convert.ToDouble(reader.ReadString());
                            Console.WriteLine(" calories={0}", calories);

                            insertFood.Parameters["@ID"].Value = Guid.NewGuid();
                            insertFood.Parameters["@FOOD_NAME"].Value = foodName;
                            insertFood.Parameters["@CATEGORY_ID"].Value = categoryID;
                            insertFood.Parameters["@CALORIES"].Value = calories;
                            insertFood.ExecuteNonQuery();
                            continue;
                        }

                    }
                }
            }
            Console.WriteLine("Done");
            Console.ReadKey();
        }
    }
}
